package logic.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.text.TextUtils;

import com.ciko.code.annotations.Column;
import com.ciko.code.annotations.Except;
import com.hzdracom.epubreader.db.BookInfoDbAdapter;
import com.shwread.http.util.Logger;

import java.io.Serializable;
import java.lang.reflect.Field;
import java.lang.reflect.Modifier;

import logic.constants.DefaultConsts;
import logic.dao.BookDao;
import logic.table.AreaTable;
import logic.table.BookMarksTable;
import logic.table.BooksTable;
import logic.table.DownloadsTable;
import logic.table.OrderTakeoutTable;
import logic.table.ShoppingCartTable;
import logic.table.UserTable;
import logic.util.logutil.LogUtil;


/**
 * 数据库
 * 
 * @author 刘宾
 * @date 2014年8月11日 上午10:14:36
 * @editor zouqp 2014-10-11
 */
public class DBHelper
        extends
        SQLiteOpenHelper
{
	public static final int                    TYPE_INSERT              = 1;
	public static final int                    TYPE_UPDATE              = 2;
	public static final int                    TYPE_DELETE              = 3;
	public static final int                    TYPE_QUERY               = 4;
	private static final String                CREATEHIGHTLIGHTINGSTYLE = "CREATE TABLE IF NOT EXISTS HighlightingStyle(" + "style_id INTEGER PRIMARY KEY," + "name TEXT NOT NULL," + "bg_color INTEGER NOT NULL)";
	private static final String                INSERTHIGHLIGHTINGSTYLE1 = "INSERT OR REPLACE INTO HighlightingStyle (style_id, name, bg_color) VALUES (1, '', 136*256*256 + 138*256 + 133)";                       // #888a85
	private static final String                INSERTHIGHLIGHTINGSTYLE2 = "INSERT OR REPLACE INTO HighlightingStyle (style_id, name, bg_color) VALUES (2, '', 245*256*256 + 121*256 + 0)";                         // #f57900
	private static final String                INSERTHIGHLIGHTINGSTYLE3 = "INSERT OR REPLACE INTO HighlightingStyle (style_id, name, bg_color) VALUES (3, '', 245*256*256 + 121*256 + 0)";                         // #f57900
	                                                                                                                                                                                                                
	private static DBHelper                    myInstance               = null;
	
	// 可读的SQLiteDatabase对象
	private static ThreadLocal<SQLiteDatabase> sqliteDatabaseR          = new ThreadLocal<SQLiteDatabase>();
	// 可写的SQLiteDatabase对象
	private static ThreadLocal<SQLiteDatabase> sqliteDatabaseW          = new ThreadLocal<SQLiteDatabase>();
	
	private Context                            context;
	
	private DBHelper(Context context) {
		super(context, DefaultConsts.DB_NAME, null, DefaultConsts.DB_VERSION);
		this.context = context;
	}
	
	private void createTables(SQLiteDatabase db) {
		db.execSQL(UserTable.getCreateTableSQL());
		db.execSQL(BooksTable.getCreateTableSQL());
		db.execSQL(DownloadsTable.getCreateTableSQL());
		
		db.execSQL(BookMarksTable.getCreateTableSQL());
		
		db.execSQL(BookInfoDbAdapter.DATATABLE_CREATE);
		
		db.execSQL(CREATEHIGHTLIGHTINGSTYLE);
		db.execSQL(INSERTHIGHLIGHTINGSTYLE1);
		db.execSQL(INSERTHIGHLIGHTINGSTYLE2);
		db.execSQL(INSERTHIGHLIGHTINGSTYLE3);
		
		//TODO 测试用
		db.execSQL(AreaTable.getCreateTableSQL());
		db.execSQL(ShoppingCartTable.getCreateTableSQL());
		db.execSQL(OrderTakeoutTable.getCreateTableSQL());
		//		for (String sql : BookMarksTable.getInsertRecommendSQL())
		//        {
		//	        db.execSQL(sql);
		//        }
		
		//		db.execSQL(AnalyticsTable.getCreateTableSQL());
		//		db.execSQL(MessageTable.getCreateTableSQL());
	}
	
	public static DBHelper getInstance(Context context) {
		if (myInstance == null)
		{
			myInstance = new DBHelper(context);
		}
		return myInstance;
	}
	
	@Override
	public void onCreate(SQLiteDatabase db) {
		LogUtil.d("DBHelper", "DBHelper onCreate");
		createTables(db);
	}
	
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		//		int version = db.getVersion();
		//		int currVersion = DefaultConsts.DB_VERSION;
		//		LogUtil.d("DBHelper", "DBHelper onUpgrade,version:"+version+",currVersion:"+currVersion);
		//		if(version>=currVersion) return;
		//		
		//        final String columns = getColumnNames(db, BooksTable.table);
		try
		{
			db.beginTransaction();
			switch (oldVersion) {
				case 1:
				case 2:
					update3(db);
				case 3:
					update4(db);
				case 4://IM账号
					update5(db);
				case 5://蓝狮子需要的user的staffid
					update6(db);
				case 6:
					update7(db);
				case 7:
					update8(db);
				case 8:
					update9(db);//epub3
				case 9:
					update10(db);//同乡会新增用户信息
			}
			db.setTransactionSuccessful();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			db.endTransaction();
		}
		db.setVersion(DefaultConsts.DB_VERSION);
		
		if (newVersion == 4)
		{
			try
			{
				//数据库版本4以前的推荐书籍都是本地写死的，升级后要清楚本地推荐书籍数据
				BookDao.getInstance(context).delReferralBooks();
			}
			catch (Exception e)
			{
				Logger.e("删除推荐书籍异常", e);
				e.printStackTrace();
			}
		}
	}
	
	/** 同乡会新增用户信息 */
	private void update10(SQLiteDatabase db) {
		db.execSQL("ALTER TABLE lq_user ADD COLUMN "+UserTable.UserColumns.COLUMN_HOMETOWN+" TEXT");
		db.execSQL("ALTER TABLE lq_user ADD COLUMN "+UserTable.UserColumns.COLUMN_LIVENOW+" TEXT");
		db.execSQL("ALTER TABLE lq_user ADD COLUMN "+UserTable.UserColumns.COLUMN_INTEREST+" TEXT");
		db.execSQL("ALTER TABLE lq_user ADD COLUMN "+UserTable.UserColumns.COLUMN_JOB+" TEXT");
		db.execSQL("ALTER TABLE lq_user ADD COLUMN "+UserTable.UserColumns.COLUMN_DESC+" TEXT");
	}
	
	/** epub3阅读器移植新增表 */
	private void update9(SQLiteDatabase db) {
		db.execSQL(BookInfoDbAdapter.DATATABLE_DROP);
		db.execSQL(BookInfoDbAdapter.DATATABLE_CREATE);
	}
	
	/**
	 * @Title: update
	 * @Description: 云信账号信息添加到用户表中
	 * @param @param db 设定文件
	 * @return void 返回类型
	 * @throws
	 */
	private void update5(SQLiteDatabase db) {
		db.execSQL("ALTER TABLE lq_user ADD COLUMN imAccount TEXT");
		db.execSQL("ALTER TABLE lq_user ADD COLUMN imToken TEXT");
	}
	
	/***
	 * 蓝狮子uid 需要staffid
	 * 
	 * @param db
	 */
	private void update6(SQLiteDatabase db) {
		db.execSQL("ALTER TABLE lq_user ADD COLUMN staffId TEXT");
	}
	
	private void update7(SQLiteDatabase db) {
		//外卖相关
		db.execSQL("DROP TABLE IF EXISTS lq_order_takeout");
		db.execSQL(OrderTakeoutTable.getCreateTableSQL());
	}
	
	private void update8(SQLiteDatabase db) {
		//电商购物车
		db.execSQL("DROP TABLE IF EXISTS lq_shopping_cart");
		db.execSQL(ShoppingCartTable.getCreateTableSQL());
		Logger.d("ShoppingCartTable onCreate");
	}
	
	private void update4(SQLiteDatabase db) {
		db.execSQL("DROP TABLE IF EXISTS lq_area");
		db.execSQL("DROP TABLE IF EXISTS lq_shopping_cart");
		//电商相关
		db.execSQL(AreaTable.getCreateTableSQL());
		db.execSQL(ShoppingCartTable.getCreateTableSQL());
		Logger.d("AreaTable onCreate");
	}
	
	private void update3(SQLiteDatabase db) {
		db.execSQL("DROP TABLE IF EXISTS Books");
		db.execSQL("DROP TABLE IF EXISTS lq_user");
		db.execSQL("DROP TABLE IF EXISTS lq_downloads");
		db.execSQL("DROP TABLE IF EXISTS Bookmarks");
		db.execSQL("DROP TABLE IF EXISTS HighlightingStyle");
		createTables(db);
	}
	
	/**
	 * 升级数据表
	 * ------------------------
	 * 总体思路：
	 * 1，将表A重命名，改名A_temp。
	 * 2，创建新表A。
	 * 3，将表A_temp的数据插入到表A。
	 * ------------------------
	 * 
	 * @param db
	 * @param tableName
	 * @param columns
	 */
	//	private void update2(SQLiteDatabase db/*, String tableName, String columns*/) {
	//		//修改原表名称
	//        String tempTableName = tableName + "_temp";  
	//        String sql = "ALTER TABLE " + tableName +" RENAME TO " + tempTableName;  
	//        db.execSQL(sql);
	//  
	//        //创建新表  
	//        db.execSQL(BooksTable.getCreateTableSQL());
	//  
	//        //将原表的数据添加到新表  
	//        sql =   "INSERT INTO " + tableName +  
	//                " (" + columns + ") " +  
	//                " SELECT " + columns + " FROM " + tempTableName;  
	//  
	//        db.execSQL(sql); 
	//  
	//        //刪除原表 
	//        db.execSQL("DROP TABLE IF EXISTS " + tempTableName);
	
	//		db.execSQL("ALTER TABLE Books ADD COLUMN ncxUrl VARCHAR(50)");
	//		db.execSQL("ALTER TABLE Books ADD COLUMN isOnline INTEGER");
	//		db.execSQL("ALTER TABLE Books ADD COLUMN syncStatus INTEGER default 0");
	//		db.execSQL("ALTER TABLE Books ADD COLUMN chapterName VARCHAR(50)");
	//		db.execSQL("ALTER TABLE Books ADD COLUMN server_id INTEGER default 0");
	//		db.execSQL("ALTER TABLE Books ADD COLUMN server_chapterid INTEGER default 0");
	//		db.execSQL("ALTER TABLE Books ADD COLUMN startTime INTEGER default 0");
	//		db.execSQL("ALTER TABLE Books ADD COLUMN endTime INTEGER default 0");
	//	}
	
	/**
	 * 升级数据表
	 * ------------------------
	 * 总体思路：
	 * 1，将表A重命名，改名A_temp。
	 * 2，创建新表A。
	 * 3，将表A_temp的数据插入到表A。
	 * ------------------------
	 * 
	 * @param db
	 * @param tableName
	 * @param columns
	 */
	//	private void update3(SQLiteDatabase db/*, String tableName, String columns*/) {
	//		//修改原表名称
	//		db.execSQL("ALTER TABLE lq_user ADD COLUMN closeTime BIGINT");
	//	}
	
	/**
	 * 获得可读的SQLiteDatabase对象
	 */
	public synchronized SQLiteDatabase getSqliteDatabaseR() {
		SQLiteDatabase dbR = sqliteDatabaseR.get();
		if (null == dbR || !dbR.isOpen())
		{
			dbR = getReadableDatabase();
			sqliteDatabaseR.set(dbR);
		}
		return dbR;
	}
	
	/**
	 * 关闭可读的SQLiteDatabase对象
	 */
	public void closeSqliteDatabaseR() {
		SQLiteDatabase dbR = sqliteDatabaseR.get();
		if (null != dbR || dbR.isOpen())
		{
			dbR.close();
			sqliteDatabaseR.set(null);
		}
	}
	
	/**
	 * 获得可写的SQLiteDatabase对象
	 */
	public synchronized SQLiteDatabase getSqliteDatabaseW() {
		SQLiteDatabase dbW = sqliteDatabaseW.get();
		if (null == dbW || !dbW.isOpen())
		{
			dbW = getWritableDatabase();
			sqliteDatabaseW.set(dbW);
		}
		return dbW;
	}
	
	/**
	 * 关闭可写的SQLiteDatabase对象
	 */
	public void closeSqliteDatabaseW() {
		SQLiteDatabase dbW = sqliteDatabaseW.get();
		if (null != dbW || dbW.isOpen())
		{
			dbW.close();
			sqliteDatabaseW.set(null);
		}
	}
	
	/** 关闭游标 */
	public void closeCursor(Cursor c) {
		if (c != null)
		{
			c.close();
			c = null;
		}
	}
	
	/**
	 * 游标 --转换为 --> Object
	 * 
	 * @param bean
	 * @param c
	 * @throws IllegalArgumentException
	 * @throws IllegalAccessException
	 */
	public void setObject(Object bean, Cursor c) throws IllegalArgumentException, IllegalAccessException {
		for (Field f : bean.getClass().getDeclaredFields())
		{
			f.setAccessible(true);
			String columnName = f.getName();
			int columnIndex = c.getColumnIndex(columnName);
			if (columnIndex == -1)
			{
				continue;
			}
			if (f.getGenericType() == Long.class || f.getGenericType() == long.class)
			{
				f.set(bean, c.getLong(columnIndex));
			}
			else if (f.getGenericType() == String.class)
			{
				String s = c.getString(columnIndex);
				f.set(bean, TextUtils.isEmpty(s) ? "" : s);
			}
			else if (f.getGenericType() == Double.class || f.getGenericType() == double.class)
			{
				f.set(bean, c.getDouble(columnIndex));
			}
			else if (f.getGenericType() == Integer.class || f.getGenericType() == int.class)
			{
				f.set(bean, c.getInt(columnIndex));
			}
			else if (f.getGenericType() == Float.class || f.getGenericType() == float.class)
			{
				f.set(bean, c.getFloat(columnIndex));
			}
			else if (f.getGenericType() == Short.class || f.getGenericType() == short.class)
			{
				f.set(bean, c.getShort(columnIndex));
			}
			else if (f.getGenericType() == Byte[].class || f.getGenericType() == byte[].class)
			{
				f.set(bean, c.getBlob(columnIndex));
			}
			else
			{ //TODO 未知类型
				f.set(bean, c.getString(columnIndex));
			}
		}
	}
	
	/**
	 * 游标 --转换为 --> 给定类型的对象
	 * 
	 * @param clazz
	 * @param c
	 * @return
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 */
	public <T extends Serializable> T getObject(Class<T> clazz, Cursor c) throws InstantiationException, IllegalAccessException {
		T t = (T) clazz.newInstance();
		setObject(t, c);
		return t;
	}
	
	/**
	 * 对象 --转换为 --> ContentValues
	 * 
	 * @param bean
	 * @return
	 * @throws IllegalArgumentException
	 * @throws IllegalAccessException
	 */
	public ContentValues getContentValues(int type, Object bean) throws IllegalArgumentException, IllegalAccessException {
		ContentValues values = new ContentValues();
		for (Field f : bean.getClass().getDeclaredFields())
		{
			f.setAccessible(true);
			if (!Modifier.isStatic(f.getModifiers()) && f.getAnnotation(Except.class) == null)
			{
				Column c = f.getAnnotation(Column.class);
				Object value = f.get(bean);
				String name = c == null ? f.getName() : (TextUtils.isEmpty(c.columnName()) ? f.getName() : c.columnName());
				if (c != null && c.primaryKey() == true)
				{
					if (type == TYPE_INSERT) values.put(name, (String) null);
				}
				else if (value instanceof Long)
				{
					values.put(name, (Long) value);
				}
				else if (value instanceof String)
				{
					values.put(name, (String) value);
				}
				else if (value instanceof Double)
				{
					values.put(name, (Double) value);
				}
				else if (value instanceof Integer)
				{
					values.put(name, (Integer) value);
				}
				else if (value instanceof Float)
				{
					values.put(name, (Float) value);
				}
				else if (value instanceof Short)
				{
					values.put(name, (Short) value);
				}
				else if (value instanceof Byte)
				{
					values.put(name, (Byte) value);
				}
				else if (value instanceof Byte[])
				{
					values.put(name, (byte[]) value);
				}
				else
				{ //TODO 未知类型
					values.put(name, value != null ? value.toString() : "");
				}
			}
		}
		return values;
	}
	
	/**
	 * 得到数据库表的列名
	 * 
	 * @param db
	 * @param tableName
	 * @return
	 */
	protected String getColumnNames(SQLiteDatabase db, String tableName) {
		//String[] columnNames = null;  
		Cursor c = null;
		String result = "";
		try
		{
			c = db.rawQuery("PRAGMA table_info(" + tableName + ")", null);
			if (null != c)
			{
				int columnIndex = c.getColumnIndex("name");
				if (-1 == columnIndex) { return null; }
				
				int index = 0;
				//columnNames = new String[c.getCount()];  
				for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext())
				{
					if (index == 0)
					{
						result = c.getString(columnIndex);
					}
					else
					{
						result = result + "," + c.getString(columnIndex);
					}
					//columnNames[index] = c.getString(columnIndex);  
					index++;
				}
			}
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			c.close();
			c = null;
		}
		//return columnNames;  
		return result;
	}
}
